package com.example.kass.service;

import com.example.kass.config.Kass;
import com.example.kass.dao.CorrectTablesDao;
import com.example.kass.model.QueryTableColumn;
import com.example.kass.model.TableColumn;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Slf4j
@Service
public class CorrectTablesService {

    @Autowired
    private Kass kass;
    @Autowired
    private CorrectTablesDao correctTablesDao;
    /**
     * 只替换符合类型的字段
     */
    final static List<String> template = Arrays.asList("varchar", "json", "char", "text", "tinytext", "mediumtext", "longtext");

    /**
     * 正则替换代码
     */
    public void correct(String tableName) {
        List<String> table_name_list = correctTablesDao.queryAllTables();
        log.info("获取所有的数据库表：总数：{}", table_name_list.size());
        if (CollectionUtils.isEmpty(table_name_list)) {
            log.warn("【{}】数据库中不存在表，程序终止！", kass.getDatabase());
            return;
        }
        if (null == tableName) {
            for (String tmpTableName : table_name_list) {
                handlePerTable(tmpTableName);
            }
        } else if (StringUtils.isNotBlank(tableName) && table_name_list.contains(tableName)) {
            //则表示存在此表，继续下一步
            handlePerTable(tableName);
        } else {
            log.warn("【{}】表不存在，程序终止！", tableName);
        }
        log.info("数据修复结束！！！");
    }

    /**
     * 处理每一张表
     *
     * @param tableName
     */
    private void handlePerTable(String tableName) {
        log.info("开始扫描 table: 【{}】~~~", tableName);
        Pattern pattern = Pattern.compile(kass.getOrginRegex());
        QueryTableColumn queryTableColumn = new QueryTableColumn();
        queryTableColumn.setTable(tableName);
        queryTableColumn.setDb(kass.getDatabase());
        List<TableColumn> tableColumns = correctTablesDao.queryTableColumn(queryTableColumn);

        //1.判断此表是否由主键，或者唯一约束，有则可以生产修改的sql语句
        String uniqueColumn = isUnique(tableColumns);
        log.info("table: 【{}】，主键：【{}】 ~~~", tableName, uniqueColumn);
        if (StringUtils.isBlank(uniqueColumn)) {
            log.warn("【{}】表不存在唯一主键，程序终止！", tableName);
            return;
        }
        //2.返回可修改的字段
        List<String> modifyField = getModifyField(tableColumns);
        if (CollectionUtils.isEmpty(modifyField)) {
            log.warn("【{}】表不存在可修改的列，程序终止！", tableName);
            return;
        }
        log.info("table: 【{}】，修改字段：【{}】 ~~~", tableName, modifyField);
        //3.分页查询数据
        long total = correctTablesDao.queryTableLine(tableName);
        long limit = kass.getLimit();
        long totalPage;
        if (total % limit == 0) {
            totalPage = total / limit;
        } else {
            totalPage = total / limit + 1;
        }
        log.info("table: 【{}】，分页查询参数【total={}, limit={}, totalPage={}】", tableName, total, limit, totalPage);
        for (int curPage = 1; curPage <= totalPage; curPage++) {
            long index = (curPage - 1) * limit;
            ///获取表数据
            List<Map<String, Object>> data = correctTablesDao.queryPageTableByName(tableName, uniqueColumn, index, limit);
            for (Map<String, Object> line : data) {
                dealPerLine(line, uniqueColumn, modifyField, pattern, tableName);
            }
        }
        log.info("结束扫描 table: 【{}】~~~", tableName);
    }

    /**
     * 处理每一行数据
     */
    private void dealPerLine(Map<String, Object> line, String uniqueColumn, List<String> modifyField, Pattern pattern,
                             String tableName) {
        //遍历每一个字段，是否需要更新
        Map<String, String> updateMap = new HashMap<>();
        Object uniqueValue = null;
        for (Map.Entry<String, Object> entry : line.entrySet()) {
            if (StringUtils.equalsIgnoreCase(entry.getKey(), uniqueColumn)) {  //获取唯一主键的值
                uniqueValue = entry.getValue();
            }
            if (modifyField.contains(entry.getKey().toUpperCase())) {  //字段需要处理
                if (isMatch((String) entry.getValue(), pattern)) { //如果匹配到，则需要处理
                    updateMap.put(entry.getKey(), getReplaceValue(entry.getValue()));
                }
            }
        }
        if (updateMap.size() > 0) { //则次行有数据需要修复
            String updateSql = generateUpdateSql(updateMap, uniqueColumn, uniqueValue, tableName);
            //fix
            log.debug("生成执行sql:【{}】", updateSql);
            int res = correctTablesDao.fixContent(updateSql);
        }
    }

    /**
     * 获取替换后的新值
     *
     * @param value
     * @return
     */
    private String getReplaceValue(Object value) {
        return ((String) value).replaceAll(kass.getOrginRegex(), kass.getReplace());
    }

    /**
     * 构建单行update sql
     */
    private String generateUpdateSql(Map<String, String> map, String uniqueColumn, Object uniqueValue, String tableName) {
        StringBuilder sb = new StringBuilder();
        sb.append("update ");
        sb.append(tableName);
        sb.append(" set ");
        StringBuilder s = new StringBuilder();
        for (Map.Entry<String, String> entry : map.entrySet()) {
            s.append(" `");
            s.append(entry.getKey());
            s.append("` = '");
            s.append(entry.getValue());
            s.append("',");
        }
        sb.append(s.toString().substring(0, s.toString().length() - 1));
        sb.append(" where `");
        sb.append(uniqueColumn);
        sb.append("` = ");
        sb.append(uniqueValue.toString());
        return sb.toString();
    }

    /**
     * 判断该字段内容是否需要更新
     *
     * @param orgin
     * @return
     */
    private boolean isMatch(String orgin, Pattern pattern) {
        Matcher m = pattern.matcher(orgin);
        if (m.find()) {  //匹配到有数据则替换
            return true;
        }
        return false;
    }

    /**
     * 判断是否有主键，或唯一约束
     *
     * @return 唯一列名
     */
    private String isUnique(List<TableColumn> tableColumns) {
        String pk = null;
        for (TableColumn tc : tableColumns) {
            if ("PRI".equals(tc.getColumnKey().toUpperCase())) {  //找出主键
                pk = tc.getColumnName();
                break;
            } else if ("UNI".equals(tc.getColumnKey().toUpperCase())) {
                pk = tc.getColumnName();
            }
        }
        return pk;
    }

    /**
     * 获取表中可替换的字段
     *
     * @param tableColumns
     */
    private List<String> getModifyField(List<TableColumn> tableColumns) {
        List<String> ret = new ArrayList<>();
        for (TableColumn tc : tableColumns) {
            if (template.contains(tc.getDataType())) {
                ret.add(tc.getColumnName().toUpperCase());
            }
        }
        return ret;
    }

}
